﻿using System;
using System.Data;
using System.Data.SqlClient;
using prjKQXS_DLL.Danh_muc;

namespace prjKQXS_DLL.Hop_dong
{
    public class HOPDONG_DAL
    {
        public DataTable Loadnhatky(string Ma_DMDonVi, int muc, int Ngay, int Thang, int Nam)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            try
            {
                string sql = "";
                if (muc == 0)
                {
                    sql = @"select DQ.*, DV.Ten_DT AS TenDV
                            from DMQUY DQ INNER JOIN DMDONVI DV ON DQ.MaDV = DV.Ma_DT
                            where datepart(day, DQ.Ngay) = @Ngay 
                                and datepart(month, DQ.Ngay) = @Thang 
                                and datepart(year, DQ.Ngay) = @Nam 
                                and rtrim(Madv) like @Ma
                            order by DQ.Ngay DESC";
                }
                else
                    if (muc == 1)
                    {
                        sql = @"select DQ.*, DV.Ten_DT AS TenDV 
                                from DMQUY DQ INNER JOIN DMDONVI DV ON DQ.MaDV = DV.Ma_DT 
                                where datepart(month, DQ.Ngay) = @Thang 
                                    and datepart(year, DQ.Ngay) = @Nam 
                                    and rtrim(Madv) like @Ma 
                                order by DQ.Ngay DESC";
                    }
                    else
                    {
                        sql = @"select DQ.*, DV.Ten_DT AS TenDV 
                                from DMQUY DQ INNER JOIN DMDONVI DV ON DQ.MaDV = DV.Ma_DT
                                where datepart(year, DQ.Ngay) = @Nam 
                                    and rtrim(Madv) like @Ma 
                                order by DQ.Ngay DESC";
                    }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.AddWithValue("@Ma", Ma_DMDonVi == "TC" ? "%" : Ma_DMDonVi);
                da.SelectCommand.Parameters.AddWithValue("@Ngay", Ngay);
                da.SelectCommand.Parameters.AddWithValue("@Thang", Thang);
                da.SelectCommand.Parameters.AddWithValue("@Nam", Nam);
                da.Fill(dt);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
            }

            return dt;
        }

        public int thanhLyHD(int id, DateTime ngay, string lydo)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            SqlCommand cmd = null;
            try
            {
                //cmd = new SqlCommand("update HOPDONG set Thanh_Ly = 1, Ngay_ThanhLy = @Ngay, Ly_Do = @Ly_Do where ID = @ID", conn);
                cmd = new SqlCommand("update HOPDONG set Ngay_ThanhLy = @Ngay, Ly_Do = @Ly_Do where ID = @ID", conn);
                cmd.Parameters.AddWithValue("@Ngay", ngay);
                cmd.Parameters.AddWithValue("@Ly_Do", lydo);
                cmd.Parameters.AddWithValue("@ID", id);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public bool checkMa_DaiLy(string ma_daiLy,int id)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlCommand cmd = new SqlCommand("Select count(*) from HOPDONG where Ma_DaiLy = @MA_DT and id <> @id", conn);
                cmd.Parameters.AddWithValue("@MA_DT", ma_daiLy);
                cmd.Parameters.AddWithValue("@id", id);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }

        public bool checkMST(string MST,int id)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                //SqlCommand cmd = new SqlCommand("Select count(*) from HOPDONG where MST = @MST and Thanh_Ly = 0 and id <> @id", conn);
                SqlCommand cmd = new SqlCommand("Select count(*) from HOPDONG where MST = @MST and ((CASE WHEN HOPDONG.Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END) = 0) and id <> @id", conn);
                cmd.Parameters.AddWithValue("@MST", MST);
                cmd.Parameters.AddWithValue("@id", id);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }

        public bool checkCMND(string CMT,int id)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                //SqlCommand cmd = new SqlCommand("Select count(*) from HOPDONG where CMT = @CMT and Thanh_Ly = 0 and id <> @id", conn);
                SqlCommand cmd = new SqlCommand("Select count(*) from HOPDONG where CMT = @CMT and ((CASE WHEN HOPDONG.Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END) = 0) and id <> @id", conn);
                cmd.Parameters.AddWithValue("@CMT", CMT);
                cmd.Parameters.AddWithValue("@id", id);
                conn.Open();
                if (cmd.ExecuteScalar().ToString() == "0")
                {
                    retVal = true;
                }
                else
                {
                    retVal = false;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
            }
            return retVal;
        }

        public bool checkSo_CT(string soct,int id)
        {
            try
            {
                SqlConnection conn = new SqlConnection(BienTc.connString);
                conn.Open();
                bool reVal;
                //string s = "Select HOPDONG.ID FROM HOPDONG LEFT OUTER JOIN LAIXUAT_DL ON HOPDONG.Ma_DaiLy = LAIXUAT_DL.Ma_DaiLy " +
                //    "where SoPhieuThu = @SoPT and Thanh_Ly = 0 and HOPDONG.id <> @id";
                string s = "Select HOPDONG.ID FROM HOPDONG LEFT OUTER JOIN LAIXUAT_DL ON HOPDONG.Ma_DaiLy = LAIXUAT_DL.Ma_DaiLy " +
                    "where SoPhieuThu = @SoPT and ((CASE WHEN HOPDONG.Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END) = 0) and HOPDONG.id <> @id";
                SqlDataAdapter da = new SqlDataAdapter(s, conn);
                da.SelectCommand.Parameters.AddWithValue("@SoPT", soct);
                da.SelectCommand.Parameters.AddWithValue("@id", id);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    reVal = true;
                }
                else
                {
                    reVal = false ;
                }
                return reVal;
            }
            catch
            {
                throw;
            }
        }

        public int insertHOPDONG(clsHopDong hopdong)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            SqlCommand cmd = null;

            string sql = "insert into dbo.[HOPDONG](Ma_DT,Ngay_HD,Ma_DMCanBo,Ten_CanBo,Noi_O,MST,CMT,Noi_Cap,Noi_Ban,So_Tien " + 
                ",Ma_DMTinhTrang,Ma_DMLoaiVe,Doanh_Thu,Ly_Do,Ma_DMLoaiHD,isPublic, isCapBac) " + 
                "values(@Ma_DT,@Ngay_HD,@Ma_DMCanBo,@Ten_CanBo,@Noi_O,@MST,@CMT,@Noi_Cap,@Noi_Ban,@So_Tien,@Ma_DMTinhTrang" +
                ",@Ma_DMLoaiVe,@Doanh_Thu,	@Ly_Do,	@Ma_DMLoaiHD,	@isPublic,@isCapBac)";
            cmd = new SqlCommand( sql, conn);
            cmd.Parameters.AddWithValue("@Ma_DT", hopdong.Ma_DT);
            cmd.Parameters.AddWithValue("@Ngay_HD", hopdong.Ngay_HD);
            cmd.Parameters.AddWithValue("@Ma_DMCanBo", hopdong.Ma_DMCanBo);
            cmd.Parameters.AddWithValue("@Ten_CanBo", hopdong.Ten_CanBo);
            cmd.Parameters.AddWithValue("@Noi_O", hopdong.Noi_O);
            cmd.Parameters.AddWithValue("@MST", hopdong.MST);
            cmd.Parameters.AddWithValue("@CMT", hopdong.CMT);
            cmd.Parameters.AddWithValue("@Noi_Cap", hopdong.Noi_Cap);
            cmd.Parameters.AddWithValue("@isCapBac", hopdong.isCapBac);
            cmd.Parameters.AddWithValue("@Noi_Ban", hopdong.Noi_Ban);
            cmd.Parameters.AddWithValue("@So_Tien", hopdong.So_Tien);
            cmd.Parameters.AddWithValue("@Ma_DMTinhTrang", hopdong.Ma_DMTinhTrang);
            cmd.Parameters.AddWithValue("@Doanh_Thu", hopdong.Doanh_Thu);
            cmd.Parameters.AddWithValue("@Ly_Do", hopdong.Ly_Do);
            cmd.Parameters.AddWithValue("@Ma_DMLoaiHD", hopdong.Ma_DMLoaiHD);
            cmd.Parameters.AddWithValue("@isPublic", hopdong.isPublic);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }

        public DataTable loadAllHopDong(string Ma_DMCanBo, bool isPublic, int Thanh_Ly)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            string sql = "";
            if (Ma_DMCanBo == "TC")
            {
                if (Thanh_Ly < 2)
                {
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                        "where isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly)";
                }
                else
                {
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG where isPublic = @isPublic";
                }
            }
            else
            {
                if (Thanh_Ly < 2)
                {
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                        "where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly)";
                }
                else
                {
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic";
                }

            }
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            if (Ma_DMCanBo != "TC")
            {
                da.SelectCommand.Parameters.AddWithValue("@Ma_DMCanBo", Ma_DMCanBo);
            }
            da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
            if (Thanh_Ly < 2)
            {
                if (Thanh_Ly == 0)
                {
                    //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                    da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                }
                else if (Thanh_Ly == 1)
                {
                    //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                    da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                }

            }
            da.Fill(dt);

            return dt;
        }

        public DataTable loadAllHopDong_Cap1LaiXuat(string Ma_DMCanBo, bool isPublic, int Thanh_Ly, bool isCapBac)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            //try
            //{
                string sql = "";
                if (Ma_DMCanBo == "TC")
                {
                    if (Thanh_Ly < 2)
                    {
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                            "where isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly)  and isCapBac=@isCapBac";
                    }
                    else
                    {
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG where isPublic = @isPublic and isCapBac=@isCapBac";
                    }
                }
                else
                {
                    if (Thanh_Ly < 2)
                    {
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                            "where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) and  isCapBac=@isCapBac";
                    }
                    else
                    {
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                            "where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic and isCapBac=@isCapBac";
                    }

                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DMCanBo != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMCanBo", Ma_DMCanBo);
                }
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.SelectCommand.Parameters.AddWithValue("@isCapBac", isCapBac);
                if (Thanh_Ly < 2)
                {
                    if (Thanh_Ly == 0)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                    }
                    else if (Thanh_Ly == 1)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                    }

                }
                da.Fill(dt);
            //}
            //catch (Exception)
            //{
            //    throw;
            //}
            //finally
            //{
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            //}

            return dt;
        }

        public DataTable loadAllHopDongbyMadaily(string Ma_DMCanBo, bool isPublic, int Thanh_Ly)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            //try
            //{
                string sql = "";
                if (Ma_DMCanBo == "TC")
                {
                    if (Thanh_Ly < 2)
                    {
                        //sql = "select * from HOPDONG where isPublic = @isPublic and Thanh_Ly=@Thanh_Ly order by Ma_DaiLy";
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                            "where isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) order by Ma_DaiLy";
                    }
                    else
                    {
                        //sql = "select * from HOPDONG where isPublic = @isPublic order by Ma_DaiLy";
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly from HOPDONG " +
                            "where isPublic = @isPublic order by Ma_DaiLy";
                    }
                }
                else
                {
                    if (Thanh_Ly < 2)
                    {
                        //sql = "select * from HOPDONG where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic and Thanh_Ly=@Thanh_Ly order by Ma_DaiLy";
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                            "from HOPDONG where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) order by Ma_DaiLy";
                    }
                    else
                    {
                        //sql = "select * from HOPDONG where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic order by Ma_DaiLy";
                        sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                            "from HOPDONG where Ma_DMCanBo = @Ma_DMCanBo and isPublic = @isPublic order by Ma_DaiLy";
                    }

                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DMCanBo != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMCanBo", Ma_DMCanBo);
                }
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                if (Thanh_Ly < 2)
                {
                    if (Thanh_Ly == 0)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                    }
                    else if (Thanh_Ly == 1)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                    }

                }
                da.Fill(dt);
            //}
            //catch (Exception)
            //{
            //    throw;
            //}
            //finally
            //{
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            //}

            return dt;
        }

        //Dung trong truong hop dac biet :))
        public DataTable loadAllHopDong2(string Ma_DMDonVi, bool isPublic, int Thanh_Ly)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            //try
            //{
                string sql = "";
                if (Thanh_Ly < 2)
                {
                    //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where HopDong.isPublic = @isPublic and Thanh_Ly=@Thanh_Ly and DMDonVi.Ma_dt = @Ma ";
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                        "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                        "where HopDong.isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) and DMDonVi.Ma_dt = @Ma ";
                }
                else
                {
                    //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma)";
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                        "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                        "where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma)";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.AddWithValue("@Ma", Ma_DMDonVi);
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                if (Thanh_Ly < 2)
                {
                    if (Thanh_Ly == 0)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                    }
                    else
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                    }
                }
                da.Fill(dt);
            //}
            //catch (Exception)
            //{
            //    throw;
            //}
            //finally
            //{
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            //}

            return dt;
        }

        public DataTable loadAllHopDong2_Cap1(string Ma_DMDonVi, bool isPublic, int Thanh_Ly, bool isCapBac, string sDLCap2 = "")
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            SqlDataAdapter da;

            //Đoạn này dùng để nạp dữ liệu cho frmDSDLTC2
            if (sDLCap2.Length > 6)
                if (sDLCap2.Substring(0, 6) == "SELECT")
                {
                    da = new SqlDataAdapter(sDLCap2, conn);
                    da.Fill(dt);
                    return dt;
                }

            string sql = "";
            if (Thanh_Ly < 2)   //<=> "Đang hoạt động" hoặc "Đã thanh lý", không phải "Tất cả"
            {
                //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where HopDong.isPublic = @isPublic and Thanh_Ly=@Thanh_Ly and DMDonVi.Ma_dt = @Ma ";
                sql = "select *, CAST(CASE WHEN HOPDONG.Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                    "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                    "where HopDong.isPublic = @isPublic and (CAST(CASE WHEN HOPDONG.Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) and DMDonVi.Ma_dt = @Ma ";
            }
            else //WHERE (CASE WHEN HOPDONG.Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END = 1)
            {
                //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma )";
                sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                    "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                    "where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma )";
            }
            da = new SqlDataAdapter(sql, conn);
            da.SelectCommand.Parameters.AddWithValue("@Ma", Ma_DMDonVi);
            da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
            if (Thanh_Ly < 2)
            {
                if (Thanh_Ly == 0)
                {
                    //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                    da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                }
                else
                {
                    //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                    da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                }
            }
            da.Fill(dt);

            return dt;
        }

        public DataTable loadAllHopDong2_Cap1ByLaiXuat(string Ma_DMDonVi, bool isPublic, int Thanh_Ly, bool isCapBac, string SQL="")
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            //try
            //{
                string sql = "";
                if (Thanh_Ly < 2)
                {
                    //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where HopDong.isPublic = @isPublic and Thanh_Ly=@Thanh_Ly and DMDonVi.Ma_dt = @Ma and isCapBac=@isCapBac";
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                        "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                        "where HopDong.isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) and DMDonVi.Ma_dt = @Ma and isCapBac=@isCapBac";
                }
                else
                {
                    //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma and isCapBac=@isCapBac)";
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                        "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                        "where isPublic = @isPublic and Ma_DMCanBo in (select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma and isCapBac=@isCapBac)";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.AddWithValue("@Ma", Ma_DMDonVi);
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.SelectCommand.Parameters.AddWithValue("@isCapBac", isCapBac);
                if (Thanh_Ly < 2)
                {
                    if (Thanh_Ly == 0)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                    }
                    else
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                    }
                }
                da.Fill(dt);
            //}
            //catch (Exception)
            //{
            //    throw;
            //}
            //finally
            //{
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            //}

            return dt;
        }

        public DataTable loadAllHopDong2byMaDaiLy(string Ma_DMDonVi, bool isPublic, int Thanh_Ly)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            DataTable dt = new DataTable();
            //try
            //{
                string sql = "";
                if (Thanh_Ly < 2)
                {
                    //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where HopDong.isPublic = @isPublic and Thanh_Ly=@Thanh_Ly and DMDonVi.Ma_dt = @Ma order by Ma_DaiLy";
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                        "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                        "where HopDong.isPublic = @isPublic and (CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) = @Thanh_Ly) and DMDonVi.Ma_dt = @Ma order by Ma_DaiLy";
                }
                else
                {
                    //sql = "select * from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma order by Ma_DaiLy)";
                    sql = "select *, CAST(CASE WHEN Ngay_ThanhLy IS NULL THEN 0 ELSE 1 END AS Bit) AS Thanh_Ly " +
                        "from HOPDONG INNER JOIN DMCanBo ON HOPDONG.Ma_DMCanBo=DMCanBo.Ma_DT INNER JOIN DMDonVi ON DMCanBo.Ma_DMDonVi=DMDonVi.Ma_DT " +
                        "where isPublic = @isPublic and Ma_DMCanBo in(select Ma_DT from DMCANBO where Ma_DMDonVi = @Ma order by Ma_DaiLy)";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.AddWithValue("@Ma", Ma_DMDonVi);
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                if (Thanh_Ly < 2)
                {
                    if (Thanh_Ly == 0)
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", false);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 0);
                    }
                    else
                    {
                        //da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", true);
                        da.SelectCommand.Parameters.AddWithValue("@Thanh_Ly", 1);
                    }
                }
                da.Fill(dt);
            //}
            //catch (Exception)
            //{
            //    throw;
            //}
            //finally
            //{
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            //}

            return dt;
        }

    }
}
